package com.css.fxfzdzzh.modules.basicData.repository.impl;

import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.modules.basicData.entity.NaturalDensity;
import com.css.fxfzdzzh.modules.basicData.entity.SeismotectionBelt;
import com.css.fxfzdzzh.modules.basicData.entity.SeismotectionZone;
import com.css.fxfzdzzh.modules.basicData.vo.QueryByVersionParam;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author Dbj
 * @date 2023年04月25日  12:13
 */
@Repository
public class SeismotectionRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void deleteBeltByVersion(String versionCode) {
        String sql = "update dzzh_sys_seismotection_belt  set del_flag = '1' where data_version='" + versionCode + "' and del_flag='"+ YNEnum.N+"'";
        jdbcTemplate.update(sql);
    }

    public void byBeltShpBatchSave(List<SeismotectionBelt> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO  dzzh_sys_seismotection_belt  (belt_id,belt_name,magnitude_limit,background_magnitude,zone_number,belt_content,data_version,create_user,create_time,del_flag,geom) " +
                "values(:beltId,:beltName,:magnitudeLimit,:backgroundMagnitude,:zoneNumber,:beltContent,:dataVersion,:createUser,:createTime,:delFlag,ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public List<SeismotectionBelt> getBeltYwData(QueryByVersionParam queryByVersionParam) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_seismotection_belt where 1=1 and data_version='"+queryByVersionParam.getVersionCode()+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        queryByDataVersionSQLBuild(queryByVersionParam, stringBuilder);
        stringBuilder.append(" order by create_time desc ");
        if (queryByVersionParam.getPage() != null && queryByVersionParam.getRows() != null) {
            stringBuilder.append(" limit ").append(queryByVersionParam.getRows()).append(" offset ").append(queryByVersionParam.getRows() * (Math.max(queryByVersionParam.getPage(), 1) - 1));
        }
        return jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SeismotectionBelt.class));
    }

    private void queryByDataVersionSQLBuild(QueryByVersionParam queryByVersionParam,StringBuilder stringBuilder) {
        if (org.apache.commons.lang3.StringUtils.isNotBlank(queryByVersionParam.getProvince())) {
            stringBuilder.append(" and province = '").append(queryByVersionParam.getProvince()).append("' ");
        }
        if (org.apache.commons.lang3.StringUtils.isNotBlank(queryByVersionParam.getCity())) {
            stringBuilder.append(" and city = '").append(queryByVersionParam.getCity()).append("' ");
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getCounty())) {
            stringBuilder.append(" and county = '").append(queryByVersionParam.getCounty()).append("' ");
        }
    }

    public Long countByBeltDataVersion(QueryByVersionParam queryByVersionParam) {
        StringBuilder sql = new StringBuilder("select count(*) from dzzh_sys_seismotection_belt where del_flag = '0' and data_version = '" + queryByVersionParam.getVersionCode() + "'");
        queryByDataVersionSQLBuild(queryByVersionParam, sql);
        return jdbcTemplate.queryForObject(sql.toString(), Long.class);
    }

    public void updateBeltLs(String versionCode, String tempVersionCode) {
        String sql = "update dzzh_sys_seismotection_belt  set del_flag = '0' ,data_version= '"+versionCode+"'  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    public void updateZoneLs(String versionCode, String tempVersionCode) {
        String sql = "update dzzh_sys_seismotection_zone  set del_flag = '0' ,data_version= '"+versionCode+"'  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    public void byZoneShpBatchSave(List<SeismotectionZone> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO  dzzh_sys_seismotection_zone  (zone_id,zone_name,zone_num,background_earthquake,belonging_zone,max_earth_quake,zone_content,data_version,create_user,create_time,del_flag,geom) " +
                "values(:zoneId,:zoneName,:zoneNum,:backgroundEarthquake,:belongingZone,:maxEarthQuake,:zoneContent,:dataVersion,:createUser,:createTime,:delFlag,ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public void deleteZoneByVersion(String versionCode) {
        String sql = "update dzzh_sys_seismotection_zone  set del_flag = '1' where data_version='" + versionCode + "' and del_flag='"+ YNEnum.N+"'";
        jdbcTemplate.update(sql);
    }

    public List<SeismotectionZone> getZoneYwData(QueryByVersionParam queryByVersionParam) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_seismotection_zone where 1=1 and data_version='"+queryByVersionParam.getVersionCode()+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        queryByDataVersionSQLBuild(queryByVersionParam, stringBuilder);
        stringBuilder.append(" order by create_time desc ");
        if (queryByVersionParam.getPage() != null && queryByVersionParam.getRows() != null) {
            stringBuilder.append(" limit ").append(queryByVersionParam.getRows()).append(" offset ").append(queryByVersionParam.getRows() * (Math.max(queryByVersionParam.getPage(), 1) - 1));
        }
        return jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SeismotectionZone.class));
    }

    public Long countByZoneDataVersion(QueryByVersionParam queryByVersionParam) {
        StringBuilder sql = new StringBuilder("select count(*) from dzzh_sys_seismotection_zone where del_flag = '0' and data_version = '" + queryByVersionParam.getVersionCode() + "'");
        queryByDataVersionSQLBuild(queryByVersionParam, sql);
        return jdbcTemplate.queryForObject(sql.toString(), Long.class);
    }

    public void updateBeltByVersion(String tempVersionCode, String versionCode, String tableName) {
        String sql = "update "+tableName+" set data_version='"+versionCode+"' where del_flag='0' and data_version='"+tempVersionCode+"'";
        jdbcTemplate.update(sql);
    }

    public Integer getBeltValidDataNum(String versionCode) {
        String sql = "select count(*) from dzzh_sys_seismotection_belt where del_flag='0' and data_version='"+versionCode+"'";
        Integer rowNum = jdbcTemplate.queryForObject(sql, Integer.class);
        if(rowNum==null){
            return 0;
        }
        return rowNum;
    }

    public Integer getZoneValidDataNum(String versionCode) {
        String sql = "select count(*) from dzzh_sys_seismotection_zone where del_flag='0' and data_version='"+versionCode+"'";
        Integer rowNum = jdbcTemplate.queryForObject(sql, Integer.class);
        if(rowNum==null){
            return 0;
        }
        return rowNum;
    }

    public SeismotectionBelt getYwBeltDetails(String id) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_seismotection_belt where 1=1 and belt_id='"+id+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        List<SeismotectionBelt> query = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SeismotectionBelt.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):new SeismotectionBelt();
    }

    public SeismotectionZone getYwZoneDetails(String id) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_seismotection_zone where 1=1 and zone_id='"+id+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        List<SeismotectionZone> query = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SeismotectionZone.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):new SeismotectionZone();
    }
}
